from openpyxl import load_workbook
from tools_pmc import today_str1, lj_bom, lj_bom_yestoday, bom_1


def difference_bom(serial_number1, ws):
    temp = bom_1(serial_number1, ws)
    temp1, temp2 = temp[0], temp[1]
    return [temp1, temp2]


def bom_change_details():
    wb_BOM_today = load_workbook(lj_bom)
    ws_BOM_today = wb_BOM_today.worksheets[0]

    list1 = [[1], [1]]
    for i in range(2, ws_BOM_today.max_row + 1):
        if list1[0][-1] != ws_BOM_today.cell(i, 1).value:
            list1[0].append(ws_BOM_today.cell(i, 1).value)
            list1[1].append(int(ws_BOM_today.cell(i, 3).value) * ws_BOM_today.cell(i, 4).value)
        else:
            list1[1][-1] += (int(ws_BOM_today.cell(i, 3).value) * ws_BOM_today.cell(i, 4).value)

    wb_BOM_yestoday = load_workbook(lj_bom_yestoday)
    ws_BOM_yestoday = wb_BOM_yestoday.worksheets[0]
    list2 = [[1], [1]]
    for i in range(2, ws_BOM_yestoday.max_row + 1):
        if list2[0][-1] != ws_BOM_yestoday.cell(i, 1).value:
            list2[0].append(ws_BOM_yestoday.cell(i, 1).value)
            list2[1].append(int(ws_BOM_yestoday.cell(i, 3).value) * ws_BOM_yestoday.cell(i, 4).value)
        else:
            list2[1][-1] += (int(ws_BOM_yestoday.cell(i, 3).value) * ws_BOM_yestoday.cell(i, 4).value)
    list3 = []
    for i in list1[0]:  # 识别出现BOM变更的品号
        for j in list2[0]:
            if i == j:
                if list1[1][list1[0].index(i)] != list2[1][list2[0].index(j)]:
                    list3.append(j)
                    break

    # list4_1 = [[], []]  # 储存前一天BOM
    # list4_2 = [[], []]  # 储存当天BOM
    list5 = [[], [], []]  # 新增、删除、变更

    for i in list3:
        list_result = [[], [], [], [], []]
        list5[0].append(i)
        list4_1 = difference_bom(i, ws_BOM_yestoday)
        list4_2 = difference_bom(i, ws_BOM_today)
        for serial_number in list4_2[0]:  # 今日新增
            try:
                list4_1[0].index(serial_number)
            except ValueError:
                list_result[0].append(serial_number)
                list_result[1].append(list4_2[1][list4_2[0].index(serial_number)])
                list_result[2].append(None)
                list_result[3].append(None)
                list_result[4].append("物料新增")
        for serial_number in list4_1[0]:  # 今日删除
            try:
                list4_2[0].index(serial_number)
            except ValueError:
                list_result[0].append(None)
                list_result[1].append(None)
                list_result[2].append(serial_number)
                list_result[3].append(list4_1[1][list4_1[0].index(serial_number)])
                list_result[4].append("物料删减")
        for serial_number_t in list4_2[0]:  # 组成数量变化
            for serial_number_y in list4_1[0]:
                if serial_number_t == serial_number_y and list4_2[1][list4_2[0].index(serial_number_t)] != \
                        list4_1[1][list4_1[0].index(serial_number_y)]:
                    list_result[0].append(serial_number_t)
                    list_result[1].append(list4_2[1][list4_2[0].index(serial_number_t)])
                    list_result[2].append(serial_number_y)
                    list_result[3].append(list4_1[1][list4_1[0].index(serial_number_t)])
                    list_result[4].append("用量变更")
        list5[1].append(list_result)
        list_result = []
        if i[0] == "2":
            for j in range(2, ws_BOM_today.max_row + 1):
                if ws_BOM_today.cell(j, 3).value == i:
                    list_result.append(ws_BOM_today.cell(j, 1).value)
        list5[2].append(list_result)

    for i in range(len(list5[0])-1, -1, -1):
        # print(i)
        if not list5[1][i][0]:
            del list5[0][i]
            del list5[1][i]
            del list5[2][i]
    print(today_str1 + ":BOM_Change_Details succeed!")
    return list5


# print(bom_change_details())
# input("Press any key to exit...")
